﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Database.Classes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString SystemName(int sId)
    {
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("Select SystemName From Systems where SystemID = @sid", conn))
            {
                cmd.Parameters.Add("@sid", SqlDbType.Int).Value = sId;
                return cmd.ExecuteScalar().ToString();
            }
        }
    }
    [Microsoft.SqlServer.Server.SqlFunction]
    public static int SystemId(string name)
    {
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("Select SystemID From Systems where SystemName Like @n", conn))
            {
                cmd.Parameters.Add("@n", SqlDbType.NVarChar).Value = name;
                object o = cmd.ExecuteScalar();
                return int.Parse(o.ToString());
            }
        }
    }
    [Microsoft.SqlServer.Server.SqlFunction]
    public static int SystemAtCoordinates(int x, int y)
    {
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("Select SystemID From Systems where X = @x and Y = @y", conn))
            {
                cmd.Parameters.Add("@x", SqlDbType.Int).Value = x;
                cmd.Parameters.Add("@y", SqlDbType.Int).Value = y;
                object o = cmd.ExecuteScalar();
                if (o == null) return -1;
                return int.Parse(o.ToString());
            }
        }
    }
};

